/*
 *    This file is part of BlobDB.
 *
 *    BlobDB is free software: you can redistribute it and/or modify
 *    it under the terms of the GNU General Public License as published by
 *    the Free Software Foundation, either version 3 of the License, or
 *    (at your option) any later version.
 *
 *    BlobDB is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *    GNU General Public License for more details.
 *
 *    You should have received a copy of the GNU General Public License
 *    along with BlobDB.  If not, see <http://www.gnu.org/licenses/>.
 *    
 *    Copyright(c) 2011 Michael Kankkonen
 *    http://www.vnetcon.org
 */


package org.vnetcon.blobdb.database.util;

import java.util.ArrayList;
import java.util.List;


import org.vnetcon.blobdb.database.SQLException;
import org.vnetcon.blobdb.database.backend.PersistenceManagerInterface;
import org.vnetcon.blobdb.database.backend.QueryInterface;
import org.vnetcon.blobdb.database.backend.TableInterface;
import org.vnetcon.blobdb.database.util.parser.CreateTableParser;
import org.vnetcon.blobdb.database.util.parser.DeleteTableParser;
import org.vnetcon.blobdb.database.util.parser.DropTableParser;
import org.vnetcon.blobdb.database.util.parser.InsertTableParser;
import org.vnetcon.blobdb.database.util.parser.SelectTableParser;
import org.vnetcon.blobdb.database.util.parser.UpdateTableParser;



public class SQLParser {

	public static String DEFAULT_SCHEMA = "USERS";
	public static String SYSTEM_SCHEMA = "SYSTEM";
	private PersistenceManagerInterface pm = null;	

	public static final String ORDER_ASC = "ASC";
	public static final String ORDER_DESC = "DESC";
	
	public static final int UNKNOWN_TYPE = -1;
	
	public static final int CREATE_DATABASE_TYPE = 1;
	public static final int DROP_DATABASE_TYPE = 2;
	
	public static final int TABLE_LIST_TYPE = 3;
	public static final int USER_LIST_TYPE = 4;
	public static final int FUNCTION_LIST_TYPE = 5;
	
	public static final int CREATE_TABLE_TYPE = 6;
	public static final int CREATE_INDEX_TYPE = 7;
	public static final int CREATE_USER_TYPE = 8;
	public static final int CREATE_FUNCTION_TYPE = 9;

	public static final int DESC_TABLE_TYPE = 10;
	public static final int DESC_INDEX_TYPE = 11;
	public static final int DESC_USER_TYPE = 12;
	public static final int DESC_FUNCTION_TYPE = 13;

	public static final int DROP_TABLE_TYPE = 14;
	public static final int DROP_INDEX_TYPE = 15;
	public static final int DROP_USER_TYPE = 16;
	public static final int DROP_FUNCTION_TYPE = 17;

	public static final int TABLE_INSERT_TYPE = 18;
	public static final int TABLE_UPDATE_TYPE = 19;
	public static final int TABLE_SELECT_TYPE = 20;
	public static final int TABLE_DELETE_TYPE = 21;

	public static final String CHAR_COL = "CHAR";
	public static final String NUMBER_COL = "NUMBER";
	public static final String DECIMAL_COL = "DECIMAL";
	public static final String DATE_COL = "DATE";
	public static final String TIME_COL = "TIME";
	public static final String FOREIGN_KEY_COL = "FOREIGN_KEY";
	
	
	private ArrayList<String> aSqlElements = null;
	private List<String> selectColumns = null;
	private List<String> subQueries = null;
	private List<String[]> orderBy = null;
	private ArrayList<String> updateColumns = null;

	private String schema = null;
	private String tableName = null;
	private ColumnInfo columnInfo = null;

	private int rowsize = -1;
	private int segmaxrows = -1;
	String rowid = null;
	int updateTableID = -1;

	private String wherePart = null;
	
	private int rangeStart = -1;
	private int rangeEnd = -1;
	
	public SQLParser(){
//		this.schema = schema;
	}

	public int getTableRowSize(){
		return this.rowsize;
	}

	public ArrayList<String> getUpdateColumns(){
		return this.updateColumns;
	}
	
	public String getRowid(){
		return this.rowid;
	}
	
	public int getTableSegMaxRows(){
		return this.segmaxrows;
	}
	
	public List<String> getSelectColumns(){
		return this.selectColumns;
	}

	public List<String> getSubQueries(){
		return this.subQueries;
	}
	
	public void setPersistentManager(PersistenceManagerInterface pm){
		this.pm = pm;
	}
	
	public String getTableName(){
		return this.tableName;
	}
	
	public ColumnInfo getColumnInfo(){
		return this.columnInfo;
	}

	private String getSchema(String tableName){
		String strRet = null;
		if(tableName.indexOf(".") > 0){
			strRet = tableName.substring(0, tableName.indexOf("."));
		}
		return strRet;
	}

	private String getTable(String tableName){
		String strRet = null;
		if(tableName.indexOf(".") > 0){
			strRet = tableName.substring(tableName.indexOf(".") + 1, tableName.length());
		}
		return strRet;
	}
	
	private int parseCreate(List<String> sql) throws SQLException {
		int iRet = UNKNOWN_TYPE;
		String strCreateElement = sql.get(1).toUpperCase();
		
		if(strCreateElement.equals("DATABASE")){
			iRet = CREATE_DATABASE_TYPE;
		}
		if(strCreateElement.equals("USER")){
			iRet = CREATE_USER_TYPE;
		}
		if(strCreateElement.equals("TABLE")){
			CreateTableParser ctp = new CreateTableParser(aSqlElements);
			this.tableName = ctp.getTable();
			
			if(this.getSchema(this.tableName) != null){
				this.schema = this.getSchema(this.tableName);
				this.tableName = this.getTable(this.tableName);
			}else{
				this.schema = DEFAULT_SCHEMA;
			}
			
			this.columnInfo = ctp.getColumnInfo();
			this.rowsize = ctp.getRowsize();
			this.segmaxrows = ctp.getSegmaxrows();
			iRet = CREATE_TABLE_TYPE;
		}
		if(strCreateElement.equals("INDEX")){
			iRet = CREATE_INDEX_TYPE;
		}
		if(strCreateElement.equals("FUNCTION")){
			iRet = CREATE_FUNCTION_TYPE;
		}
		
		if(iRet == UNKNOWN_TYPE){
			throw new SQLException("Unkonwn create type: " + strCreateElement);
		}
		
		return iRet;
	}

	private int parseDesc(List<String> sql) throws SQLException {
		int iRet = UNKNOWN_TYPE;
		String strCreateElement = sql.get(1).toUpperCase();
		String strElement = sql.get(2).toUpperCase();

		if(strCreateElement.equals("TABLE")){
			this.tableName = sql.get(2);
			
			if(this.getSchema(this.tableName) != null){
				this.schema = this.getSchema(this.tableName);
				this.tableName = this.getTable(this.tableName);
			}else{
				this.schema = DEFAULT_SCHEMA;
			}
			iRet = DESC_TABLE_TYPE;
		}
		if(strCreateElement.equals("USER")){
				iRet = DESC_USER_TYPE;
		}
		if(strCreateElement.equals("INDEX")){
				iRet = DESC_INDEX_TYPE;
		}
		if(strCreateElement.equals("FUNCTION")){
				iRet = DESC_FUNCTION_TYPE;
		}
		return iRet;
	}
	
	private int parseDrop(List<String> sql) throws SQLException {
		int iRet = UNKNOWN_TYPE;
		String strCreateElement = sql.get(1).toUpperCase();
		String strElement = sql.get(2).toUpperCase();

		if(strCreateElement.equals("DATABASE")){
			iRet = DROP_DATABASE_TYPE;
		}
		if(strCreateElement.equals("TABLE")){
			DropTableParser ctp = new DropTableParser(aSqlElements);
			this.tableName = ctp.getTable();
			if(this.getSchema(this.tableName) != null){
				this.schema = this.getSchema(this.tableName);
				this.tableName = this.getTable(this.tableName);
			}else{
				this.schema = DEFAULT_SCHEMA;
			}
			iRet = DROP_TABLE_TYPE;
		}
		if(strCreateElement.equals("USER")){
			iRet = DROP_USER_TYPE;
		}
		if(strCreateElement.equals("INDEX")){
			iRet = DROP_INDEX_TYPE;
		}
		if(strCreateElement.equals("FUNCTION")){
			iRet = DROP_FUNCTION_TYPE;
		}
		return iRet;
	}

	private int parseInsert(List<String> sql) throws SQLException {
		int iRet = UNKNOWN_TYPE;
		String strInto = sql.get(1).toUpperCase();
		String strTable = sql.get(2).toUpperCase();

		if(strInto.equals("INTO")){
			this.columnInfo = null; //TODO: getColumnInfo for table;
			InsertTableParser ctp = new InsertTableParser(aSqlElements);
			ctp.initTable();
			this.tableName = ctp.getTable();
			if(this.getSchema(this.tableName) != null){
				this.schema = this.getSchema(this.tableName);
				this.tableName = this.getTable(this.tableName);
			}else{
				this.schema = DEFAULT_SCHEMA;
			}
			ctp.setColumnInfo(getColumnInfoForTable(this.tableName));
			ctp.initColumns();
			this.columnInfo = ctp.getColumnInfo();
			iRet = TABLE_INSERT_TYPE;
		}
		return iRet;
	}

	private ColumnInfo getColumnInfoForTable(String tableName) throws SQLException {
		ColumnInfo ci = new ColumnInfo();
		TableInterface table = null;
		byte infoBytes[] = null;
	    QueryInterface query = pm.newQuery(TableInterface.class);
	    query.setFilter("tableName == tableNameParam && schema == schemaParam");
//	    query.setFilter("schema == schemaParam");
	    query.declareParameters("String tableNameParam, String schemaParam");

	    List<TableInterface> results = (List<TableInterface>)query.execute(tableName, schema);
	    if(results.size() < 1){
	    	throw new SQLException("Table does not exists: " + schema + "." + tableName);
	    }

	    table = results.get(0);
	    updateTableID = table.getTableID();
	    this.rowsize = table.getRowsize();
	    this.segmaxrows = table.getMaxrows();
	    this.rowsize = table.getRowsize();
		infoBytes = table.getBytes();
		ci.parseInfo(infoBytes);
		return ci;
	}
	
	public int getUpdateTableID(){
		return this.updateTableID;
	}
	
	private int parseUpdate(List<String> sql) throws SQLException {
		int iRet = UNKNOWN_TYPE;
		String strTable = sql.get(1).toUpperCase();
		UpdateTableParser ctp = new UpdateTableParser(aSqlElements);
		ctp.initTable();
		this.tableName = ctp.getTableName();
		if(this.getSchema(this.tableName) != null){
			this.schema = this.getSchema(this.tableName);
			this.tableName = this.getTable(this.tableName);
		}else{
			this.schema = DEFAULT_SCHEMA;
		}
		ctp.setColumnInfo(getColumnInfoForTable(this.tableName));
		ctp.init();
		this.columnInfo = ctp.getColumnInfo();
		this.rowid = ctp.getRowId();
		this.updateColumns = ctp.getUpdateColumns();
		iRet = TABLE_UPDATE_TYPE;
		return iRet;
	}
	
	private int parseDelete(List<String> sql) throws SQLException {
		int iRet = UNKNOWN_TYPE;
		String strTable = sql.get(1).toUpperCase();
		DeleteTableParser ctp = new DeleteTableParser(aSqlElements);
		ctp.initTable();
		this.tableName = ctp.getTableName();
		if(this.getSchema(this.tableName) != null){
			this.schema = this.getSchema(this.tableName);
			this.tableName = this.getTable(this.tableName);
		}else{
			this.schema = DEFAULT_SCHEMA;
		}
		ctp.setColumnInfo(getColumnInfoForTable(this.tableName));
		ctp.init();
		this.columnInfo = ctp.getColumnInfo();
		this.rowid = ctp.getRowId();
		this.updateColumns = ctp.getUpdateColumns();
		iRet = TABLE_DELETE_TYPE;
		return iRet;
	}

	private int parseSelect(List<String> sql) throws SQLException {
		int iRet = UNKNOWN_TYPE;
		SelectTableParser ctp = new SelectTableParser(aSqlElements);
		this.tableName = ctp.getTable();
		if(this.getSchema(this.tableName) != null){
			this.schema = this.getSchema(this.tableName);
			this.tableName = this.getTable(this.tableName);
		}else{
			this.schema = DEFAULT_SCHEMA;
		}
		this.selectColumns = ctp.getSelectColumns();
		this.subQueries = ctp.getSubQueries();
		this.wherePart = ctp.getWherePart();
		this.orderBy = ctp.getOrderBy();
		this.rowid = ctp.getRowId();
		this.rangeStart = ctp.getRangeStart();
		this.rangeEnd = ctp.getRangeEnd();
//		this.columnInfo = ctp.getColumnInfo();
		iRet = TABLE_SELECT_TYPE;
		return iRet;
	}

	public int getRangeStart(){
		return this.rangeStart;
	}
	
	public int getRangeEnd(){
		return this.rangeEnd;
	}

	public List<String> getSqlElements(){
		return this.aSqlElements;
	}
	
	private ArrayList<String> getSqlElements(String sql){
		String s = sql.trim();
		ArrayList<String> aRet = new ArrayList<String>();
		ArrayList<String> aRetUpper = new ArrayList<String>();
		int i = 0;
		String strBuf = "";
		boolean bInString = false;
		
		while(i < s.length()){
			
			if(s.charAt(i) == ' ' && !bInString){
				if(!strBuf.trim().equals("")){
					aRet.add(strBuf);
				}
				strBuf = "";
				i++;
				continue;
			}

			if(s.charAt(i) == ';' && !bInString){
				if(!strBuf.trim().equals("")){
					aRet.add(strBuf);
				}
				aRet.add(";");
				strBuf = "";
				i++;
				continue;
			}
			
			
			if(s.charAt(i) == ',' && !bInString){
				if(!strBuf.trim().equals("")){
					aRet.add(strBuf);
				}
				aRet.add(",");
				strBuf = "";
				i++;
				continue;
			}

			if(s.charAt(i) == '(' && !bInString){
				if(!strBuf.trim().equals("")){
					aRet.add(strBuf);
				}
				aRet.add("(");
				strBuf = "";
				i++;
				continue;
			}

			if(s.charAt(i) == ')' && !bInString){
				if(!strBuf.trim().equals("")){
					aRet.add(strBuf);
				}
				aRet.add(")");
				strBuf = "";
				i++;
				continue;
			}
			
			strBuf += s.charAt(i);
			
			if(s.charAt(i) == '\'' && !bInString){
				bInString = true;
				i++;
				continue;
			}

			if(s.charAt(i) == '\'' && bInString){
				if(i < s.length() - 1 && s.charAt(i + 1) != '\''){
					bInString = false;
				}else{
					strBuf += "'";
					i++;
				}
			}
			
			i++;
		}

		if(!strBuf.trim().equals("")){
			aRet.add(strBuf);
		}

		i = 0;
		while(i < aRet.size()){
			if(!aRet.get(i).startsWith("'")){
				String strUpper = aRet.get(i);
				String strDot = ".";
				if(strUpper.indexOf(".") > -1){
					String parts[] = strUpper.split("\\.");
					int ii = 1;
					strUpper = parts[0].toUpperCase();
					while(ii < parts.length){
						strUpper = strUpper + strDot + parts[ii];
						ii++;
					}
				}else{
					strUpper = strUpper.toUpperCase();
				}
				aRetUpper.add(strUpper);
			}else{
				String strUpper = aRet.get(i);
				aRetUpper.add(strUpper);
			}
			i++;
		}
		
		return aRetUpper;
	}
	
	public int parse(String sql) throws SQLException {
		int iRet = UNKNOWN_TYPE;
		ArrayList<String> s = getSqlElements(sql);
		this.aSqlElements = s;

		/*
		int i = 0;
		while(i < s.size()){
			System.out.println("elem: " + s.get(i));
			i++;
		}
		*/
		String stmt = s.get(0).toUpperCase();
		
		if(stmt.equals("CREATE")){
			iRet = this.parseCreate(s);
			return iRet;
		}

		if(stmt.equals("DESC")){
			iRet = this.parseDesc(s);
			return iRet;
		}

		if(stmt.equals("DROP")){
			iRet = this.parseDrop(s);
			return iRet;
		}

		if(stmt.equals("INSERT")){
			iRet = this.parseInsert(s);
			return iRet;
		}

		if(stmt.equals("UPDATE")){
			iRet = this.parseUpdate(s);
			return iRet;
		}
		
		if(stmt.equals("DELETE")){
			iRet = this.parseDelete(s);
			return iRet;
		}

		if(stmt.equals("SELECT")){
			iRet = this.parseSelect(s);
			return iRet;
		}
		
		
		throw new SQLException("Unknown statement: " + stmt);
	}
	
/*	
	public static void main(String[] args){
		System.out.println("SQLParser v 0.1 beta");
		System.out.println("Copyright(c) 2010 Michael Kankkonen");
		try{
			
			String sql1 = "CREATE TABLE TEST(FNAME TEXT, LNAME TEXT, AGE LONG, WEIGHT DOUBLE)";
			String sql2 = "INSERT INTO TEST(FNAME, LNAME, AGE, WEIGHT) VALUES ('Michael', 'Kankkonen', 16, 65)";
			String sql3 = "UPDATE TEST SET FNAME = 'Miska', LNAME = 'Kankkonen', AGE = 12, WEIGHT = 50";
			String sql4 = "DELETE TEST WHERE FNAME = 'Miska' AND AGE = 12";
			String sql5 = "select T.LNAME, C.EMAIL FROM TEST T, CONTACT C WHERE FNAME = 'Miska  ''pikkumies''   Kankkonen' AND AGE = 12 AND T.ID = C.ID";
			
			
			String sql6 = "SELECT ";
						sql6 += " ROWID, ";
						sql6 += " P.DAY_OF_BIRTH, ";
						sql6 += " P.AGE, ";
						sql6 += " P.TALL, ";
						sql6 += " FUNCTION.trim(P.LNAME) AS LASTNAME, ";
						sql6 += " (SELECT C.PHONE, C.EMAIL FROM CONTACT C WHERE C.PERSON_ID = P.PERSON_ID) AS CONTACTS ";
						sql6 += " FROM ";
						sql6 += " PERSON P ";
						sql6 += " WHERE ";
						sql6 += " INDEX.PERSON_NAME = ('Kankkonen', 'Michael') ";
						sql6 += " AND ";
						sql6 += " FUNCTION.ageAndTall(P.AGE, P.TALL) > 0 ";
						sql6 += " AND ";
						sql6 += " LIMIT(0, 50) ";
						sql6 += " ORDER BY ";
						sql6 += " INDEX.PERSON_AGE ";
			
			
			SQLParser sp = new SQLParser("test");
			int iSqlType = sp.parse(sql6);
			System.out.println("sql type: " + iSqlType);
			
		}catch(Exception e){
			System.out.println("SQLParser error in main: " + e);
		}
		
		
	}
*/
	public String getWherePart() {
		return wherePart;
	}

	public String getSchema() {
		return schema;
	}
	
	public List<String[]> getOrderBy() {
		return this.orderBy;
	}
	
	
}
